package com.um.springboot.starter.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

@Slf4j
@SuppressWarnings("deprecation")
public class ExcelUtil {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

	/**
	 * 把数据写入excel文件中
	 *  columnName 表头的名
	 * @param list 数据列表
	 * @param properties  需要写入excel的属性列表
	 * @param map 键值转换字典(属性为键，键值字典为值)
	 * @param mapFormat 时间格式化字典(属性为键，日期格式化字符串为值)
	 */
	@SuppressWarnings("unchecked")
	public static void write(String path,List<String> columns,List<Object> list,List properties,Map<String,Map<String,String>> map,
			Map<String,String> mapFormat,Map mapZero){
		Workbook workbook = new SXSSFWorkbook();
		try{
			Sheet sheet = workbook.createSheet();
			Row row = sheet.createRow(0);

			CellStyle numberStyle = workbook.createCellStyle();
			numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

			//列头
			for(int index = 0;index < columns.size();index++){
				Cell cell = row.createCell(index);
				cell.setCellType(CellType.STRING);
				cell.setCellValue(columns.get(index));
			}
			//内容
			for(int rowIndex = 1;rowIndex <= list.size();rowIndex ++){
				row = sheet.createRow(rowIndex);
				Map mapEntity = (Map)list.get(rowIndex-1);
				for(int column = 0;column < properties.size();column ++){
					String property = (String)properties.get(column);
					Cell cell = row.createCell(column);

					//如果值需要转换就转换
					if(map.containsKey(property)){
						Map<String,String> mapChange = map.get(property);
						cell.setCellType(CellType.STRING);
						cell.setCellValue(mapChange.get(mapEntity.get(property)+""));
					}else if(mapFormat.containsKey(property)){//如果是日期，就需要格式化
						if(mapEntity.get(property) == null) {
							cell.setCellType(CellType.STRING);
							cell.setCellValue("");
						}
						else{
							String format = mapFormat.get(property);
							if(format.startsWith("timeFormat")){
								int time = Integer.parseInt(mapEntity.get(property)+"");
								String strTime = "";
								if(time <= 0){
									strTime = "00:00";
								}else{
									int hours = time / 3600;
									int surplusSeconds = time % 3600;
									int minutes = 0, seconds = 0;
									if (surplusSeconds >= 60) {
										minutes = surplusSeconds / 60;
										seconds = surplusSeconds % 60;
									} else {
										seconds = surplusSeconds;
									}
									 strTime = (hours < 10 ? "0" + hours : hours) + ":"
											+ (minutes < 10 ? "0" + minutes : minutes) + ":"
											+ (seconds < 10 ? "0" + seconds : seconds);
									if (hours == 0) {
										strTime = (minutes < 10 ? "0" + minutes : minutes) + ":"
												+ (seconds < 10 ? "0" + seconds : seconds);
									}
								}
								cell.setCellType(CellType.STRING);
								cell.setCellValue(strTime);
							}else if(format.equals("number")){
								cell.setCellType(CellType.STRING);
								cell.setCellValue(Float.parseFloat(mapEntity.get(property)+""));
								cell.setCellStyle(numberStyle);
							}else if(format.equals("int")){
								cell.setCellType(CellType.STRING);
								cell.setCellValue(Float.parseFloat(mapEntity.get(property)+""));
							}else{
								cell.setCellType(CellType.STRING);
								if(mapEntity.get(property) instanceof LocalDateTime){
									DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);
									cell.setCellValue(formatter.format((LocalDateTime)mapEntity.get(property)));
								}else{
									cell.setCellValue(DateUtil.getDateString((Date)mapEntity.get(property),format));
								}
							}
						}
					}else{
						if(mapEntity.get(property) == null){
							cell.setCellType(CellType.STRING);
							if(mapZero.containsKey(property)){
								cell.setCellValue("0");
							}else{
								cell.setCellValue(StringUtil.getTrim(mapEntity.get(property)));
							}
						}else{
							cell.setCellType(CellType.STRING);
							cell.setCellValue(StringUtil.getTrim(mapEntity.get(property)));
						}
					}
				}
			}

			FileUtil.mkdirs(path);
			FileOutputStream output = new FileOutputStream(path);
			workbook.write(output);
			output.flush();
			output.close();
		} catch (Exception e) {
			log.error("", e);
		} finally {
			try {
				workbook.close();
			} catch (Exception e) {
				log.warn("", e);
			}
		}
	}

	/**
	 * 把数据写入excel文件中
	 *  columnName 表头的名
	 * @param list 数据列表
	 * @param properties  需要写入excel的属性列表
	 * @param map 键值转换字典(属性为键，键值字典为值)
	 * @param mapFormat 时间格式化字典(属性为键，日期格式化字符串为值)
	 */
	@SuppressWarnings("unchecked")
	public static void writeEntity(String path,List<String> columns,List<Object> list,List properties,Map<String,Map<String,String>> map,
							 Map<String,String> mapFormat,Map mapZero){
		Workbook workbook = new SXSSFWorkbook();
		try{
			Sheet sheet = workbook.createSheet();
			Row row = sheet.createRow(0);

			CellStyle numberStyle = workbook.createCellStyle();
			numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

			//列头
			for(int index = 0;index < columns.size();index++){
				Cell cell = row.createCell(index);
				cell.setCellType(CellType.STRING);
				cell.setCellValue(columns.get(index));
			}
			//内容
			for(int rowIndex = 1;rowIndex <= list.size();rowIndex ++){
				row = sheet.createRow(rowIndex);
				Object mapEntity = list.get(rowIndex-1);
				for(int column = 0;column < properties.size();column ++){
					String property = (String)properties.get(column);
					String propertyName = NameUtils.getPropertyName(property);
					Object value = ReflectUtil.getValue(mapEntity,propertyName);

					Cell cell = row.createCell(column);

					//如果值需要转换就转换
					if(map.containsKey(property)){
						Map<String,String> mapChange = map.get(property);
						cell.setCellType(CellType.STRING);
						cell.setCellValue(mapChange.get(value+""));
					}else if(mapFormat.containsKey(property)){//如果是日期，就需要格式化
						if(value == null) {
							cell.setCellType(CellType.STRING);
							cell.setCellValue("");
						}
						else{
							String format = mapFormat.get(property);
							if(format.startsWith("timeFormat")){
								int time = Integer.parseInt(value+"");
								String strTime = "";
								if(time <= 0){
									strTime = "00:00";
								}else{
									int hours = time / 3600;
									int surplusSeconds = time % 3600;
									int minutes = 0, seconds = 0;
									if (surplusSeconds >= 60) {
										minutes = surplusSeconds / 60;
										seconds = surplusSeconds % 60;
									} else {
										seconds = surplusSeconds;
									}
									strTime = (hours < 10 ? "0" + hours : hours) + ":"
											+ (minutes < 10 ? "0" + minutes : minutes) + ":"
											+ (seconds < 10 ? "0" + seconds : seconds);
									if (hours == 0) {
										strTime = (minutes < 10 ? "0" + minutes : minutes) + ":"
												+ (seconds < 10 ? "0" + seconds : seconds);
									}
								}
								cell.setCellType(CellType.STRING);
								cell.setCellValue(strTime);
							}else if(format.equals("number")){
								cell.setCellType(CellType.STRING);
								cell.setCellValue(Float.parseFloat(value+""));
								cell.setCellStyle(numberStyle);
							}else if(format.equals("int")){
								cell.setCellType(CellType.STRING);
								cell.setCellValue(Float.parseFloat(value+""));
							}else{
								cell.setCellType(CellType.STRING);
								if(value instanceof LocalDateTime){
									DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);
									cell.setCellValue(formatter.format((LocalDateTime)value));
								}else{
									cell.setCellValue(DateUtil.getDateString((Date)value,format));
								}
							}
						}
					}else{
						if(value == null){
							cell.setCellType(CellType.STRING);
							if(mapZero.containsKey(property)){
								cell.setCellValue("0");
							}else{
								cell.setCellValue(StringUtil.getTrim(value));
							}
						}else{
							cell.setCellType(CellType.STRING);
							cell.setCellValue(StringUtil.getTrim(value));
						}
					}
				}
			}

			// 自动调整列宽
//			Row tempRow = sheet.getRow(0);
//			if (tempRow!= null) {
//				for (int j = 0; j < tempRow.getLastCellNum(); j++) {
//					sheet.autoSizeColumn(j);
//				}
//			}

			FileUtil.mkdirs(path);
			FileOutputStream output = new FileOutputStream(path);
			workbook.write(output);
			output.flush();
			output.close();
		} catch (Exception e) {
			log.error("", e);
		} finally {
			try {
				workbook.close();
			} catch (Exception e) {
				log.warn("", e);
			}
		}
	}


	/**
	 * 把数据写入excel文件中
	 *  columnName 表头的名
	 * @param list 数据列表
	 * @param properties  需要写入excel的属性列表
	 * @param map 键值转换字典(属性为键，键值字典为值)
	 * @param mapFormat 时间格式化字典(属性为键，日期格式化字符串为值)
	 */
	@SuppressWarnings("unchecked")
	public static <M> void writeEntity(String path,List<String> columns,List<M> list,List properties,Map<String,Map<String,String>> map,
								   Map<String,String> mapFormat,Map mapZero,boolean formatPrice){
		Workbook workbook = new SXSSFWorkbook();
		try{
			Sheet sheet = workbook.createSheet();
			Row row = sheet.createRow(0);

			CellStyle numberStyle = workbook.createCellStyle();
			numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));

			CellStyle stringStyle = workbook.createCellStyle();
			stringStyle.setWrapText(true);

			//列头
			for(int index = 0;index < columns.size();index++){
				Cell cell = row.createCell(index);
				cell.setCellType(CellType.STRING);
				cell.setCellValue(columns.get(index));
			}
			//内容
			for(int rowIndex = 1;rowIndex <= list.size();rowIndex ++){
				row = sheet.createRow(rowIndex);
				Object mapEntity = list.get(rowIndex-1);
				for(int column = 0;column < properties.size();column ++){
					String property = (String)properties.get(column);
					String propertyName = NameUtils.getPropertyName(property);
					Object value = ReflectUtil.getValue(mapEntity,propertyName);

					Cell cell = row.createCell(column);

					//如果值需要转换就转换
					if(map.containsKey(property)){
						Map<String,String> mapChange = map.get(property);
						cell.setCellType(CellType.STRING);
						cell.setCellValue(mapChange.get(value+""));
						if(cell.getStringCellValue().contains("\n")){
							cell.setCellStyle(stringStyle);
						}
					}else if(mapFormat.containsKey(property)){//如果是日期，就需要格式化
						if(value == null) {
							cell.setCellType(CellType.STRING);
							cell.setCellValue("");
						}
						else{
							String format = mapFormat.get(property);
							if(format.startsWith("timeFormat")){
								int time = Integer.parseInt(value+"");
								String strTime = "";
								if(time <= 0){
									strTime = "00:00";
								}else{
									int hours = time / 3600;
									int surplusSeconds = time % 3600;
									int minutes = 0, seconds = 0;
									if (surplusSeconds >= 60) {
										minutes = surplusSeconds / 60;
										seconds = surplusSeconds % 60;
									} else {
										seconds = surplusSeconds;
									}
									strTime = (hours < 10 ? "0" + hours : hours) + ":"
											+ (minutes < 10 ? "0" + minutes : minutes) + ":"
											+ (seconds < 10 ? "0" + seconds : seconds);
									if (hours == 0) {
										strTime = (minutes < 10 ? "0" + minutes : minutes) + ":"
												+ (seconds < 10 ? "0" + seconds : seconds);
									}
								}
								cell.setCellType(CellType.STRING);
								cell.setCellValue(strTime);
							}else if(format.equals("number")){
								if(formatPrice) {
									cell.setCellType(CellType.NUMERIC);
									cell.setCellValue(StringUtil.toInt(value)/100.0);
									cell.setCellStyle(numberStyle);
								}else{
									cell.setCellType(CellType.NUMERIC);
									cell.setCellValue(Float.parseFloat(value + ""));
									cell.setCellStyle(numberStyle);
								}
							}else if(format.equals("int")){
								cell.setCellType(CellType.NUMERIC);
								cell.setCellValue(Float.parseFloat(value+""));
							}else{
								cell.setCellType(CellType.STRING);
								if(value instanceof LocalDateTime){
									DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);
									cell.setCellValue(formatter.format((LocalDateTime)value));
								}else{
									cell.setCellValue(DateUtil.getDateString((Date)value,format));
								}
							}
						}
					}else{
						if(value == null){
							cell.setCellType(CellType.STRING);
							if(mapZero.containsKey(property)){
								cell.setCellValue("0");
							}else{
								cell.setCellValue(StringUtil.getTrim(value));
								if(cell.getStringCellValue().contains("\n")){
									cell.setCellStyle(stringStyle);
								}
							}
						}else{
							cell.setCellType(CellType.STRING);
							cell.setCellValue(StringUtil.getTrim(value));
							if(cell.getStringCellValue().contains("\n")){
								cell.setCellStyle(stringStyle);
							}
						}
					}
				}
			}
			// 自动调整列宽
//			Row tempRow = sheet.getRow(0);
//			if (tempRow!= null) {
//				for (int j = 0; j < tempRow.getLastCellNum(); j++) {
//					sheet.autoSizeColumn(j);
//				}
//			}

			FileUtil.mkdirs(path);
			FileOutputStream output = new FileOutputStream(path);
			workbook.write(output);
			output.flush();
			output.close();
		} catch (Exception e) {
			log.error("", e);
		} finally {
			try {
				workbook.close();
			} catch (Exception e) {
				log.warn("", e);
			}
		}
	}

	/**
	 *
	 * <p>Title: readExecl</p>
	 * <p>Description:读取Execl</p>
	 * @author yangxz
	 */
	public static List<Map>  read(String path,String[]properties,Map<String,Map<String,String>> map) {
		List<Map>  targetList = null;
		if(FileUtil.exists(path)){
			String[] split = path.split("\\.");
			if(OFFICE_EXCEL_2003_POSTFIX.equals(split[split.length-1])){
				targetList = readXls(path,properties,map);
			}else if(OFFICE_EXCEL_2010_POSTFIX.equals(split[split.length-1])){
				targetList = readXlsx(path,properties,map);
			}
		}
		return targetList;
	}

	/**
	 * 读取excel文件，通过反射生成一个列表对象
	 * @param path  文件路径
	 * @param properties 类属性
	 * @param map 把名称转换成对应的值字典
     * @param type excel类型
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private static List<Map> readExcel(String path,String[]properties,Map<String,Map<String,String>> map,String type){
		List<Map> list = new ArrayList<>();
		Workbook workbook = null;
		try{
            workbook = OFFICE_EXCEL_2003_POSTFIX.equals(type)
                    ? new HSSFWorkbook(new FileInputStream(path))
                    : new XSSFWorkbook(new FileInputStream(path));
			Sheet sheet = workbook.getSheetAt(0);
			int rows = sheet.getPhysicalNumberOfRows();
			for(int rowIndex = 1;rowIndex < rows;rowIndex ++){
				Row row = sheet.getRow(rowIndex);
				Cell cellF = row.getCell(0);
				String firstColumnValue = "";
				if(cellF != null && cellF.getCellType() == CellType.NUMERIC){
					BigDecimal dbF = new BigDecimal(row.getCell(0).getNumericCellValue());
					firstColumnValue = dbF.toPlainString();
				}else{
					firstColumnValue = row.getCell(0).getStringCellValue();
				}
				//终止条件：每行的第一个单元格必须有值，没有则终止退出
				if(StringUtil.getTrim(firstColumnValue).equals(""))
					break;
				Map obj = new Hashtable();
				for(int columnIndex = 0;columnIndex < properties.length;columnIndex++){
					String property = properties[columnIndex];
					Cell cell = row.getCell(columnIndex);

					String value = "";
					if(cell != null && cell.getCellType() == CellType.NUMERIC){
						BigDecimal db = new BigDecimal(cell.getNumericCellValue());
						value = db.toPlainString();
					}
					else if(cell != null)
						value = cell.getStringCellValue();

					if(map.containsKey(property)){
						Map<String,String> mapChange = map.get(property);
						obj.put(property, mapChange.get(value)+"");
					}else
						obj.put(property, value);
				}
				list.add(obj);
			}
		} catch (Exception e) {
			log.error("", e);
		} finally {
			try {
				if (workbook != null) workbook.close();
			} catch (Exception e) {
				log.warn("", e);
			}
		}
		return list;
	}

	/**
	 * 读取excel文件，通过反射生成一个列表对象
	 * @param path  文件路径
	 * @param properties 类属性
	 * @param map 把名称转换成对应的值字典
	 * @return
	 */
	public static List<Map> readXls(String path,String[]properties,Map<String,Map<String,String>> map){
		return readExcel(path, properties, map, OFFICE_EXCEL_2003_POSTFIX);
	}

    /**
     * 读取excel文件，通过反射生成一个列表对象
     * @param path  文件路径
     * @param properties 类属性
     * @param map 把名称转换成对应的值字典
     * @return
     */
	public static List<Map> readXlsx(String path,String[]properties,Map<String,Map<String,String>> map) {
        return readExcel(path, properties, map, OFFICE_EXCEL_2010_POSTFIX);
	}

	/**
	 * 读取excel文件，通过反射生成一个列表对象
	 * @param path  文件路径
	 * @param properties 类属性
	 * @param map 把名称转换成对应的值字典
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Map> readCSV(String path,String[]properties,Map<String,Map<String,String>> map){
		List<Map> list = new ArrayList<Map>();
		try{
			InputStreamReader reader = new InputStreamReader(new FileInputStream(path), "GBK");
			BufferedReader br = new BufferedReader(reader);
			String line = null;
			while((line = br.readLine()) != null){
				String[] values = line.split(",");
				Map entity = new Hashtable();
				for(int columnIndex = 0;columnIndex < properties.length;columnIndex++){
					String property = properties[columnIndex];

					String value = "";
					if(columnIndex < values.length)
						value = values[columnIndex];
					if(map.containsKey(property)){
						Map<String,String> mapChange = map.get(property);
						entity.put(property, mapChange.get(value+"")+"");
					}else
						entity.put(property, value);
				}
				list.add(entity);
			}


		}catch(Exception e){
			log.error("", e);
		}
		return list;
	}

}
